﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TarreOA.TarreOAModels;

namespace TarreOA.TarreOADAL
{
    public class EmpEmailMappingtbDAO
    {
        //保存邮件发送信息
        public int saveSendEmailInfo(string sendUserLoginName, string inceptUserLoginName, string emailTextContent, DateTime sendDateTime)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "insert into EmpEmailMappingtb values(@sendUserLoginName, @inceptUserLoginName, @emailTextContent, @SendDateTime, default)";
                SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@sendUserLoginName", sendUserLoginName));
                sqlCmd.Parameters.Add(new SqlParameter("@inceptUserLoginName", inceptUserLoginName));
                sqlCmd.Parameters.Add(new SqlParameter("@emailTextContent", emailTextContent));
                sqlCmd.Parameters.Add(new SqlParameter("@SendDateTime", sendDateTime));
                sqlCmd.ExecuteNonQuery();
                sql = "insert into EmpEmailMappingtb2 values(@suln, @iuln, @etc, @sdt, default)";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@suln", sendUserLoginName));
                sqlCmd.Parameters.Add(new SqlParameter("@iuln", inceptUserLoginName));
                sqlCmd.Parameters.Add(new SqlParameter("@etc", emailTextContent));
                sqlCmd.Parameters.Add(new SqlParameter("@sdt", sendDateTime));
                sqlCmd.ExecuteNonQuery();
                string sql2 = "select @@identity as curIndex";
                sqlCmd = new SqlCommand(sql2, sqlConn);
                count = int.Parse(sqlCmd.ExecuteScalar().ToString());                
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }

        //根据发件人查询邮件信息
        public List<EmpEmailMappingtb> findBySendUserLoginName(string suln)
        {
            List<EmpEmailMappingtb> list = null;
            SqlConnection sqlConn = null;
            SqlDataReader sqlDr = null;
            FileUploadMappingtbDAO fileUploadMappingtbDAO = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select EmpEmailMappingtb.*, usertb.username as 接收人 from EmpEmailMappingtb, usertb " + 
                    "where EmpEmailMappingtb.SendUserLoginName = @suln and usertb.loginname = EmpEmailMappingtb.InceptUserLoginName" + 
                    " order by SendDateTime desc";
                SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@suln", suln));
                sqlDr = sqlCmd.ExecuteReader();
                list = new List<EmpEmailMappingtb>();
                //UserDAO userDAO = new UserDAO();
                fileUploadMappingtbDAO = new FileUploadMappingtbDAO();
                while (sqlDr.Read())
                {
                    EmpEmailMappingtb empEmailMapping = new EmpEmailMappingtb();
                    empEmailMapping.EmpEmailId = (int)sqlDr["EmpEmailId"];
                    //empEmailMapping.SendUser = userDAO.FindByLoginName(sqlDr["SendUserLoginName"].ToString());
                    //empEmailMapping.InceptUser = userDAO.FindByLoginName(sqlDr["InceptUserLoginName"].ToString());
                    empEmailMapping.InceptUserName = (string) sqlDr["接收人"];
                    empEmailMapping.SendUserName = "-";
                    empEmailMapping.EmialTextContent = sqlDr["EmailTextContent"].ToString();
                    //empEmailMapping.AffixFiles = fileUploadMappingtbDAO.findByEmailId((int)sqlDr["EmpEmailId"]);
                    empEmailMapping.SendDateTime = (DateTime)sqlDr["SendDateTime"];
                    empEmailMapping.ReadState = (int)sqlDr["ReadState"];
                    list.Add(empEmailMapping);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }

        //根据收件人查询邮件信息
        public List<EmpEmailMappingtb> findByInceptUserLoginName(string iuln)
        {
            List<EmpEmailMappingtb> list = null;
            SqlConnection sqlConn = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                //string sql = "select * from EmpEmailMappingtb where InceptUserLoginName = @iuln order by SendDateTime desc";
                string sql = "select EmpEmailMappingtb2.*, usertb.username as 发送人 from EmpEmailMappingtb2, usertb " +
                   "where EmpEmailMappingtb2.InceptUserLoginName = @iuln and usertb.loginname = EmpEmailMappingtb2.SendUserLoginName" +
                   " order by SendDateTime desc";
                SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@iuln", iuln));
                sqlDr = sqlCmd.ExecuteReader();
                list = new List<EmpEmailMappingtb>();
                //UserDAO userDAO = new UserDAO();
                FileUploadMappingtbDAO fileUploadMappingtbDAO = new FileUploadMappingtbDAO();
                while (sqlDr.Read())
                {
                    EmpEmailMappingtb empEmailMapping = new EmpEmailMappingtb();
                    empEmailMapping.EmpEmailId = (int)sqlDr["EmpEmailId"];
                    //empEmailMapping.SendUser = userDAO.FindByLoginName(sqlDr["SendUserLoginName"].ToString());
                    //empEmailMapping.InceptUser = userDAO.FindByLoginName(sqlDr["InceptUserLoginName"].ToString());
                    empEmailMapping.SendUserName = (string) sqlDr["发送人"];
                    empEmailMapping.InceptUserName = "-";
                    empEmailMapping.EmialTextContent = sqlDr["EmailTextContent"].ToString();
                    //empEmailMapping.AffixFiles = fileUploadMappingtbDAO.findByEmailId((int)sqlDr["EmpEmailId"]);
                    empEmailMapping.SendDateTime = (DateTime)sqlDr["SendDateTime"];
                    empEmailMapping.ReadState = (int)sqlDr["ReadState"];
                    list.Add(empEmailMapping);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }

        //根据邮件编号查找邮件信息
        public EmpEmailMappingtb findByEmpEmailId(int eid, string state)
        {
            EmpEmailMappingtb empEmailMapping = null;
            SqlConnection sqlConn = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from EmpEmailMappingtb where EmpEmailId = @eid";
                if(state.Equals("incept"))
                {
                    sql = "select * from EmpEmailMappingtb2 where EmpEmailId = @eid";
                }
                SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@eid", eid));
                sqlDr = sqlCmd.ExecuteReader();
                UserDAO userDAO = new UserDAO();
                FileUploadMappingtbDAO fileUploadMappingtbDAO = new FileUploadMappingtbDAO();
                sqlDr.Read();
                empEmailMapping = new EmpEmailMappingtb();
                empEmailMapping.EmpEmailId = (int)sqlDr["EmpEmailId"];
                empEmailMapping.SendUser = userDAO.FindByLoginName(sqlDr["SendUserLoginName"].ToString());
                empEmailMapping.InceptUser = userDAO.FindByLoginName(sqlDr["InceptUserLoginName"].ToString());
                empEmailMapping.EmialTextContent = sqlDr["EmailTextContent"].ToString();
                empEmailMapping.AffixFiles = fileUploadMappingtbDAO.findByEmailId((int)sqlDr["EmpEmailId"]);
                empEmailMapping.SendDateTime = (DateTime)sqlDr["SendDateTime"];
                empEmailMapping.ReadState = (int)sqlDr["ReadState"];
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return empEmailMapping;
        }

        //根据邮件编号更改邮件阅读状态
        public int updateEmailInfo(int eid, string state)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "update EmpEmailMappingtb set ReadState = 1 where EmpEmailId = @eid";
                if("incept".Equals(state))
                {
                    sql = "update EmpEmailMappingtb2 set ReadState = 1 where EmpEmailId = @eid";
                }
                SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@eid", eid));
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }

        //根据邮件编号删除邮件信息
        public int delEmailInfo(int eid, string state)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "delete from EmpEmailMappingtb where EmpEmailId = @eid";
                if("incept".Equals(state))
                {
                    sql = "delete from EmpEmailMappingtb where EmpEmailId = @eid";
                }
                SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@eid", eid));
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }
    }
}
